#!/usr/bin/env python
# -*- coding: UTF-8 -*
# Copyright (c) 2022 OceanBase
# OceanBase Diagnostic Tool is licensed under Mulan PSL v2.
# You can use this software according to the terms and conditions of the Mulan PSL v2.
# You may obtain a copy of Mulan PSL v2 at:
#          http://license.coscl.org.cn/MulanPSL2
# THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
# EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
# MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
# See the Mulan PSL v2 for more details.

"""
@time: 2022/11/29
@file: sql_meta.py
@desc:
"""


class GlobalSqlMeta:
    _sql_dict = {}

    def _init(self):
        global _sql_dict
        self._sql_dict = {}

    def set_value(self, key, value):
        self._sql_dict[key] = value

    def get_value(self, key):
        try:
            return self._sql_dict[key]
        except:
            print('get' + key + 'failed\r\n')

    def rm_value(self, key):
        try:
            return self._sql_dict.pop(key)
        except:
            print('delete' + key + 'failed\r\n')


sql_dict = GlobalSqlMeta()

sql_dict.set_value(
    "sql_audit_by_trace_id_limit1_mysql",
    '''
    select /*+ sql_audit */ 
    trace_id,
    query_sql,
    RETURN_ROWS,
    from_unixtime(REQUEST_TIME/1000000) REQUEST_TIME,
    from_unixtime((REQUEST_TIME + ELAPSED_TIME)/1000000) REQUEST_END_TIME,
    ELAPSED_TIME,
    TENANT_NAME,
    user_name,
    db_name,
    plan_id,
    tenant_id,
    version() mysql_version,
    svr_ip,
    svr_port
    from oceanbase.##REPLACE_SQL_AUDIT_TABLE_NAME##
    where query_sql != '' and trace_id='##REPLACE_TRACE_ID##' order by REQUEST_TIME desc limit 1
    '''
)

sql_dict.set_value(
    "sql_audit_by_trace_id_limit1_oracle",
    '''
    select * from 
    (select /*+ sql_audit */ 
    trace_id,
    query_sql, 
    RETURN_ROWS, 
    REQUEST_TIME, 
    REQUEST_TIME + ELAPSED_TIME REQUEST_END_TIME, 
    ELAPSED_TIME, 
    TENANT_NAME, 
    user_name, 
    db_name , 
    plan_id, 
    tenant_id,
    banner oracle_version, 
    svr_ip, 
    svr_port 
    from sys.##REPLACE_SQL_AUDIT_TABLE_NAME##, V$VERSION
    where length(query_sql)>4 and trace_id='##REPLACE_TRACE_ID##' order by REQUEST_TIME desc) where rownum < 2
    '''
)

sql_dict.set_value(
        "sql_audit_item_mysql",
        '`SVR_IP`,`SVR_PORT`,`REQUEST_ID`,`SQL_EXEC_ID`,`TRACE_ID`,`SID`,`CLIENT_IP`,`CLIENT_PORT`,`TENANT_ID`,'
        '`EFFECTIVE_TENANT_ID`,`TENANT_NAME`,`USER_ID`,`USER_NAME`,`USER_CLIENT_IP`,`DB_ID`,`DB_NAME`,`SQL_ID`,'
        '`QUERY_SQL`,`PLAN_ID`,`AFFECTED_ROWS`,`RETURN_ROWS`,`PARTITION_CNT`,`RET_CODE`,`QC_ID`,`DFO_ID`,`SQC_ID`,'
        '`WORKER_ID`,`EVENT`,`P1TEXT`,`P1`,`P2TEXT`,`P2`,`P3TEXT`,`P3`,`LEVEL`,`WAIT_CLASS_ID`,`WAIT_CLASS`,`STATE`,'
        '`WAIT_TIME_MICRO`,`TOTAL_WAIT_TIME_MICRO`,`TOTAL_WAITS`,`RPC_COUNT`,`PLAN_TYPE`,`IS_INNER_SQL`,'
        '`IS_EXECUTOR_RPC`,`IS_HIT_PLAN`,`REQUEST_TIME`,`ELAPSED_TIME`,`NET_TIME`,`NET_WAIT_TIME`,`QUEUE_TIME`,'
        '`DECODE_TIME`,`GET_PLAN_TIME`,`EXECUTE_TIME`,`APPLICATION_WAIT_TIME`,`CONCURRENCY_WAIT_TIME`,'
        '`USER_IO_WAIT_TIME`,`SCHEDULE_TIME`,`ROW_CACHE_HIT`,`BLOOM_FILTER_CACHE_HIT`,`BLOCK_CACHE_HIT`,'
        '`BLOCK_INDEX_CACHE_HIT`,`DISK_READS`,`RETRY_CNT`,`TABLE_SCAN`,`CONSISTENCY_LEVEL`,`MEMSTORE_READ_ROW_COUNT`,'
        '`SSSTORE_READ_ROW_COUNT`,`REQUEST_MEMORY_USED`,`EXPECTED_WORKER_COUNT`,`USED_WORKER_COUNT`,`PS_STMT_ID`,'
        '`TRANSACTION_HASH`,`REQUEST_TYPE`,`IS_BATCHED_MULTI_STMT`,`OB_TRACE_INFO`,`PLAN_HASH` '
)

sql_dict.set_value(
    "sql_audit_item_oracle",
    '"SVR_IP","SVR_PORT","REQUEST_ID","SQL_EXEC_ID","TRACE_ID","SID","CLIENT_IP","CLIENT_PORT","TENANT_ID",'
    '"EFFECTIVE_TENANT_ID","TENANT_NAME","USER_ID","USER_NAME","USER_CLIENT_IP","DB_ID","DB_NAME","SQL_ID",'
    '"QUERY_SQL","PLAN_ID","AFFECTED_ROWS","RETURN_ROWS","PARTITION_CNT","RET_CODE","QC_ID","DFO_ID","SQC_ID",'
    '"WORKER_ID","EVENT","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","LEVEL","WAIT_CLASS_ID","WAIT_CLASS","STATE",'
    '"WAIT_TIME_MICRO","TOTAL_WAIT_TIME_MICRO","TOTAL_WAITS","RPC_COUNT","PLAN_TYPE","IS_INNER_SQL",'
    '"IS_EXECUTOR_RPC","IS_HIT_PLAN","REQUEST_TIME","ELAPSED_TIME","NET_TIME","NET_WAIT_TIME","QUEUE_TIME",'
    '"DECODE_TIME","GET_PLAN_TIME","EXECUTE_TIME","APPLICATION_WAIT_TIME","CONCURRENCY_WAIT_TIME",'
    '"USER_IO_WAIT_TIME","SCHEDULE_TIME","ROW_CACHE_HIT","BLOOM_FILTER_CACHE_HIT","BLOCK_CACHE_HIT",'
    '"BLOCK_INDEX_CACHE_HIT","DISK_READS","RETRY_CNT","TABLE_SCAN","CONSISTENCY_LEVEL","MEMSTORE_READ_ROW_COUNT",'
    '"SSSTORE_READ_ROW_COUNT","REQUEST_MEMORY_USED","EXPECTED_WORKER_COUNT","USED_WORKER_COUNT","PS_STMT_ID",'
    '"TRANSACTION_HASH","REQUEST_TYPE","IS_BATCHED_MULTI_STMT","OB_TRACE_INFO","PLAN_HASH" '
)

sql_dict.set_value(
        "sql_audit_item_mysql_obversion4",
        '`SVR_IP`,`SVR_PORT`,`REQUEST_ID`,`SQL_EXEC_ID`,`TRACE_ID`,`SID`,`CLIENT_IP`,`CLIENT_PORT`,`TENANT_ID`,'
        '`EFFECTIVE_TENANT_ID`,`TENANT_NAME`,`USER_ID`,`USER_NAME`,`USER_CLIENT_IP`,`DB_ID`,`DB_NAME`,`SQL_ID`,'
        '`QUERY_SQL`,`PLAN_ID`,`AFFECTED_ROWS`,`RETURN_ROWS`,`PARTITION_CNT`,`RET_CODE`,`QC_ID`,`DFO_ID`,`SQC_ID`,'
        '`WORKER_ID`,`EVENT`,`P1TEXT`,`P1`,`P2TEXT`,`P2`,`P3TEXT`,`P3`,`LEVEL`,`WAIT_CLASS_ID`,`WAIT_CLASS`,`STATE`,'
        '`WAIT_TIME_MICRO`,`TOTAL_WAIT_TIME_MICRO`,`TOTAL_WAITS`,`RPC_COUNT`,`PLAN_TYPE`,`IS_INNER_SQL`,'
        '`IS_EXECUTOR_RPC`,`IS_HIT_PLAN`,`REQUEST_TIME`,`ELAPSED_TIME`,`NET_TIME`,`NET_WAIT_TIME`,`QUEUE_TIME`,'
        '`DECODE_TIME`,`GET_PLAN_TIME`,`EXECUTE_TIME`,`APPLICATION_WAIT_TIME`,`CONCURRENCY_WAIT_TIME`,'
        '`USER_IO_WAIT_TIME`,`SCHEDULE_TIME`,`ROW_CACHE_HIT`,`BLOOM_FILTER_CACHE_HIT`,`BLOCK_CACHE_HIT`,'
        '`DISK_READS`,`RETRY_CNT`,`TABLE_SCAN`,`CONSISTENCY_LEVEL`,`MEMSTORE_READ_ROW_COUNT`,'
        '`SSSTORE_READ_ROW_COUNT`,`REQUEST_MEMORY_USED`,`EXPECTED_WORKER_COUNT`,`USED_WORKER_COUNT`,'
        '`TX_ID`,`REQUEST_TYPE`,`IS_BATCHED_MULTI_STMT`,`OB_TRACE_INFO`,`PLAN_HASH` '
)

sql_dict.set_value(
    "sql_audit_item_oracle_obversion4",
    '"SVR_IP","SVR_PORT","REQUEST_ID","SQL_EXEC_ID","TRACE_ID","SID","CLIENT_IP","CLIENT_PORT","TENANT_ID",'
    '"EFFECTIVE_TENANT_ID","TENANT_NAME","USER_ID","USER_NAME","USER_CLIENT_IP","DB_ID","DB_NAME","SQL_ID",'
    '"QUERY_SQL","PLAN_ID","AFFECTED_ROWS","RETURN_ROWS","PARTITION_CNT","RET_CODE","QC_ID","DFO_ID","SQC_ID",'
    '"WORKER_ID","EVENT","P1TEXT","P1","P2TEXT","P2","P3TEXT","P3","LEVEL","WAIT_CLASS_ID","WAIT_CLASS","STATE",'
    '"WAIT_TIME_MICRO","TOTAL_WAIT_TIME_MICRO","TOTAL_WAITS","RPC_COUNT","PLAN_TYPE","IS_INNER_SQL",'
    '"IS_EXECUTOR_RPC","IS_HIT_PLAN","REQUEST_TIME","ELAPSED_TIME","NET_TIME","NET_WAIT_TIME","QUEUE_TIME",'
    '"DECODE_TIME","GET_PLAN_TIME","EXECUTE_TIME","APPLICATION_WAIT_TIME","CONCURRENCY_WAIT_TIME",'
    '"USER_IO_WAIT_TIME","SCHEDULE_TIME","ROW_CACHE_HIT","BLOOM_FILTER_CACHE_HIT","BLOCK_CACHE_HIT",'
    '"DISK_READS","RETRY_CNT","TABLE_SCAN","CONSISTENCY_LEVEL","MEMSTORE_READ_ROW_COUNT",'
    '"SSSTORE_READ_ROW_COUNT","REQUEST_MEMORY_USED","EXPECTED_WORKER_COUNT","USED_WORKER_COUNT",'
    '"TX_ID","REQUEST_TYPE","IS_BATCHED_MULTI_STMT","OB_TRACE_INFO","PLAN_HASH" '
)

sql_dict.set_value(
    "sql_plan_monitor_dfo_op_oracle",
    '''
    SELECT
      AVG("ROWS") EST_ROWS, /*0 */
      plan_monitor.PLAN_DEPTH,
      plan_monitor.PLAN_LINE_ID,
      PLAN_OPERATION,
      COUNT(*) PARALLEL,
      MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME, /* 5 */
      MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
      MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
      MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
      MIN(FIRST_REFRESH_TS) MIN_FIRST_REFRESH_TS,
      MAX(LAST_REFRESH_TS) MAX_LAST_REFRESH_TS, /*10 */
      MIN(FIRST_CHANGE_TS) MIN_FIRST_CHANGE_TS,
      MAX(LAST_CHANGE_TS) MAX_LAST_CHANGE_TS,
      AVG(REFRESH_TS) AVG_REFRESH_TIME,
      MAX(REFRESH_TS) MAX_REFRESH_TIME,
      MIN(REFRESH_TS) MIN_REFRESH_TIME,/*15 */
      AVG(CHANGE_TS) AVG_CHANGE_TIME,
      MAX(CHANGE_TS) MAX_CHANGE_TIME,
      MIN(CHANGE_TS) MIN_CHANGE_TIME,
      SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
      SUM(STARTS) TOTAL_RESCAN_TIMES,/* 20 */
      MAX(OTHERSTAT_1_ID) OTHERSTAT_1_ID,
      SUM(OTHERSTAT_1_VALUE) SUM_STAT_1,
      MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
      MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
      AVG(OTHERSTAT_1_VALUE) AVG_STAT_1, /* 25 */
      MAX(OTHERSTAT_2_ID) OTHERSTAT_2_ID,
      SUM(OTHERSTAT_2_VALUE) SUM_STAT_2,
      MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
      MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
      AVG(OTHERSTAT_2_VALUE) AVG_STAT_2, /* 30 */
      MAX(OTHERSTAT_3_ID) OTHERSTAT_3_ID,
      SUM(OTHERSTAT_3_VALUE) SUM_STAT_3,
      MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
      MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
      AVG(OTHERSTAT_3_VALUE) AVG_STAT_3, /* 35 */
      MAX(OTHERSTAT_4_ID) OTHERSTAT_4_ID,
      SUM(OTHERSTAT_4_VALUE) SUM_STAT_4,
      MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
      MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
      AVG(OTHERSTAT_4_VALUE) AVG_STAT_4, /* 40 */
      MAX(OTHERSTAT_5_ID) OTHERSTAT_5_ID,
      SUM(OTHERSTAT_5_VALUE) SUM_STAT_5,
      MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
      MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
      AVG(OTHERSTAT_5_VALUE) AVG_STAT_5, /* 45*/
      MAX(OTHERSTAT_6_ID) OTHERSTAT_6_ID,
      SUM(OTHERSTAT_6_VALUE) SUM_STAT_6,
      MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
      MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
      AVG(OTHERSTAT_6_VALUE) AVG_STAT_6 /* 50 */
    FROM
    (
    select
      PLAN_DEPTH,
      PLAN_LINE_ID,
      PLAN_OPERATION,
      OUTPUT_ROWS,
      STARTS,
      FIRST_REFRESH_TIME,
      LAST_REFRESH_TIME,
      FIRST_CHANGE_TIME,
      LAST_CHANGE_TIME,
      OTHERSTAT_1_ID,
      OTHERSTAT_2_ID,
      OTHERSTAT_3_ID,
      OTHERSTAT_4_ID,
      OTHERSTAT_5_ID,
      OTHERSTAT_6_ID,
      OTHERSTAT_1_VALUE,
      OTHERSTAT_2_VALUE,
      OTHERSTAT_3_VALUE,
      OTHERSTAT_4_VALUE,
      OTHERSTAT_5_VALUE,
      OTHERSTAT_6_VALUE,
      24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) FIRST_REFRESH_TS,
      24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) LAST_REFRESH_TS,
      24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) FIRST_CHANGE_TS,
      24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) LAST_CHANGE_TS,
      24 * 3600 * extract(day FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + 3600 * extract(hour FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + 60 * extract(minute FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + extract(second FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) REFRESH_TS,
      24 * 3600 * extract(day FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + 3600 * extract(hour FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + 60 * extract(minute FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + extract(second FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) CHANGE_TS
    from
      sys.gv$sql_plan_monitor
      where
        trace_id = '##REPLACE_TRACE_ID##'
    
    ) plan_monitor
    LEFT JOIN
    (
     SELECT "ROWS", PLAN_LINE_ID FROM sys.##REPLACE_PLAN_EXPLAIN_TABLE_NAME## WHERE plan_id = ##REPLACE_PLAN_ID## AND tenant_id = ##REPLACE_TENANT_ID##
    ) plan_explain
    ON
      plan_monitor.PLAN_LINE_ID = plan_explain.PLAN_LINE_ID
    GROUP BY
      plan_monitor.PLAN_LINE_ID, PLAN_OPERATION, plan_monitor.PLAN_DEPTH
    ORDER BY
      plan_monitor.PLAN_LINE_ID ASC;
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_dfo_op_mysql",
    '''
        select
          AVG(ROWS) EST_ROWS, /*0*/
          plan_monitor.PLAN_DEPTH PLAN_DEPTH,
          plan_monitor.PLAN_LINE_ID PLAN_LINE_ID,
          PLAN_OPERATION,
          COUNT(*) PARALLEL,
          MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,/*5*/
          MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
          MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
          MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
          UNIX_TIMESTAMP(MIN(FIRST_REFRESH_TIME)) MIN_FIRST_REFRESH_TS,
          UNIX_TIMESTAMP(MAX(LAST_REFRESH_TIME)) MAX_LAST_REFRESH_TS, /*10*/
          UNIX_TIMESTAMP(MIN(FIRST_CHANGE_TIME)) MIN_FIRST_CHANGE_TS,
          UNIX_TIMESTAMP(MAX(LAST_CHANGE_TIME)) MAX_LAST_CHANGE_TS,
          AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) AVG_REFRESH_TIME,
          MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MAX_REFRESH_TIME,
          MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MIN_REFRESH_TIME, /*15 */
          AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) AVG_CHANGE_TIME,
          MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MAX_CHANGE_TIME,
          MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MIN_CHANGE_TIME,
          SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
          SUM(STARTS) TOTAL_RESCAN_TIMES,/* 20 */
          MAX(OTHERSTAT_1_ID) OTHERSTAT_1_ID,
          SUM(OTHERSTAT_1_VALUE) SUM_STAT_1,
          MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
          MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
          AVG(OTHERSTAT_1_VALUE) AVG_STAT_1, /* 25 */
          MAX(OTHERSTAT_2_ID) OTHERSTAT_2_ID,
          SUM(OTHERSTAT_2_VALUE) SUM_STAT_2,
          MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
          MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
          AVG(OTHERSTAT_2_VALUE) AVG_STAT_2, /* 30 */
          MAX(OTHERSTAT_3_ID) OTHERSTAT_3_ID,
          SUM(OTHERSTAT_3_VALUE) SUM_STAT_3,
          MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
          MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
          AVG(OTHERSTAT_3_VALUE) AVG_STAT_3, /* 35 */
          MAX(OTHERSTAT_4_ID) OTHERSTAT_4_ID,
          SUM(OTHERSTAT_4_VALUE) SUM_STAT_4,
          MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
          MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
          AVG(OTHERSTAT_4_VALUE) AVG_STAT_4, /* 40 */
          MAX(OTHERSTAT_5_ID) OTHERSTAT_5_ID,
          SUM(OTHERSTAT_5_VALUE) SUM_STAT_5,
          MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
          MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
          AVG(OTHERSTAT_5_VALUE) AVG_STAT_5, /* 45*/
          MAX(OTHERSTAT_6_ID) OTHERSTAT_6_ID,
          SUM(OTHERSTAT_6_VALUE) SUM_STAT_6,
          MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
          MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
          AVG(OTHERSTAT_6_VALUE) AVG_STAT_6 /* 50 */
        from
        (
          select * FROM oceanbase.gv$sql_plan_monitor
        where
          trace_id = '##REPLACE_TRACE_ID##'
        ) plan_monitor
        LEFT JOIN
        (
         SELECT ROWS, PLAN_LINE_ID FROM oceanbase.##REPLACE_PLAN_EXPLAIN_TABLE_NAME## WHERE plan_id = ##REPLACE_PLAN_ID## AND tenant_id = ##REPLACE_TENANT_ID##
        ) plan_explain
        ON
          plan_monitor.PLAN_LINE_ID = plan_explain.PLAN_LINE_ID
        GROUP BY
          plan_monitor.PLAN_LINE_ID, plan_monitor.PLAN_OPERATION
        ORDER BY
          plan_monitor.PLAN_LINE_ID ASC
        '''
)

sql_dict.set_value(
    "sql_plan_monitor_svr_agg_template_oracle",
    '''
        SELECT
          PLAN_DEPTH,
          PLAN_LINE_ID,
          PLAN_OPERATION,
          COUNT(*) PARALLEL,
          MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,
          MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
          MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
          MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
          MIN(FIRST_REFRESH_TS) MIN_FIRST_REFRESH_TS,
          MAX(LAST_REFRESH_TS) MAX_LAST_REFRESH_TS,
          MIN(FIRST_CHANGE_TS) MIN_FIRST_CHANGE_TS,
          MAX(LAST_CHANGE_TS) MAX_LAST_CHANGE_TS,
          AVG(REFRESH_TS) AVG_REFRESH_TIME,
          MAX(REFRESH_TS) MAX_REFRESH_TIME,
          MIN(REFRESH_TS) MIN_REFRESH_TIME,
          AVG(CHANGE_TS) AVG_CHANGE_TIME,
          MAX(CHANGE_TS) MAX_CHANGE_TIME,
          MIN(CHANGE_TS) MIN_CHANGE_TIME,
          SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
          SUM(STARTS) TOTAL_RESCAN_TIMES,
          SVR_IP,
          SVR_PORT,
          MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
          MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
          AVG(OTHERSTAT_1_VALUE) AVG_STAT_1,
          MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
          MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
          AVG(OTHERSTAT_2_VALUE) AVG_STAT_2,
          MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
          MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
          AVG(OTHERSTAT_3_VALUE) AVG_STAT_3,
          MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
          MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
          AVG(OTHERSTAT_4_VALUE) AVG_STAT_4,
          MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
          MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
          AVG(OTHERSTAT_5_VALUE) AVG_STAT_5,
          MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
          MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
          AVG(OTHERSTAT_6_VALUE) AVG_STAT_6
        FROM
        (
        select
          PLAN_DEPTH,
          SVR_IP,
          SVR_PORT,
          PLAN_LINE_ID,
          PLAN_OPERATION,
          OUTPUT_ROWS,
          STARTS,
          FIRST_REFRESH_TIME,
          LAST_REFRESH_TIME,
          FIRST_CHANGE_TIME,
          LAST_CHANGE_TIME,
          OTHERSTAT_1_VALUE,
          OTHERSTAT_2_VALUE,
          OTHERSTAT_3_VALUE,
          OTHERSTAT_4_VALUE,
          OTHERSTAT_5_VALUE,
          OTHERSTAT_6_VALUE,
          24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) FIRST_REFRESH_TS,
          24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) LAST_REFRESH_TS,
          24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) FIRST_CHANGE_TS,
          24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) LAST_CHANGE_TS,
          24 * 3600 * extract(day FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + 3600 * extract(hour FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + 60 * extract(minute FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + extract(second FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) REFRESH_TS,
          24 * 3600 * extract(day FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + 3600 * extract(hour FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + 60 * extract(minute FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + extract(second FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) CHANGE_TS
        from
          sys.gv$sql_plan_monitor
          where
            trace_id = '##REPLACE_TRACE_ID##'
        ) tmp
        GROUP BY
          PLAN_LINE_ID,PLAN_OPERATION,PLAN_DEPTH,SVR_IP,SVR_PORT
        ORDER BY
          ##REPLACE_ORDER_BY##
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_svr_agg_template_mysql",
    '''
        select
          PLAN_DEPTH,
          PLAN_LINE_ID,
          PLAN_OPERATION,
          COUNT(*) PARALLEL,
          MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,
          MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
          MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
          MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
          UNIX_TIMESTAMP(MIN(FIRST_REFRESH_TIME)) MIN_FIRST_REFRESH_TS,
          UNIX_TIMESTAMP(MAX(LAST_REFRESH_TIME)) MAX_LAST_REFRESH_TS,
          UNIX_TIMESTAMP(MIN(FIRST_CHANGE_TIME)) MIN_FIRST_CHANGE_TS,
          UNIX_TIMESTAMP(MAX(LAST_CHANGE_TIME)) MAX_LAST_CHANGE_TS,
          AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) AVG_REFRESH_TIME,
          MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MAX_REFRESH_TIME,
          MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MIN_REFRESH_TIME,
          AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) AVG_CHANGE_TIME,
          MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MAX_CHANGE_TIME,
          MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MIN_CHANGE_TIME,
          SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
          SUM(STARTS) TOTAL_RESCAN_TIMES,
          SVR_IP,
          SVR_PORT,
          MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
          MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
          AVG(OTHERSTAT_1_VALUE) AVG_STAT_1,
          MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
          MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
          AVG(OTHERSTAT_2_VALUE) AVG_STAT_2,
          MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
          MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
          AVG(OTHERSTAT_3_VALUE) AVG_STAT_3,
          MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
          MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
          AVG(OTHERSTAT_4_VALUE) AVG_STAT_4,
          MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
          MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
          AVG(OTHERSTAT_5_VALUE) AVG_STAT_5,
          MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
          MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
          AVG(OTHERSTAT_6_VALUE) AVG_STAT_6
        from
          oceanbase.gv$sql_plan_monitor
        where
          trace_id = '##REPLACE_TRACE_ID##'
        GROUP BY
          PLAN_LINE_ID,PLAN_OPERATION,SVR_IP,SVR_PORT
        ORDER BY
          ##REPLACE_ORDER_BY##
        '''
)

sql_dict.set_value(
    "sql_plan_monitor_detail_template_oracle",
    '''
        select
          PLAN_DEPTH, /*0*/
          SVR_IP,
          SVR_PORT,
          PROCESS_NAME,
          PLAN_LINE_ID,
          PLAN_OPERATION,/*5*/
          OUTPUT_ROWS,
          STARTS RESCAN_TIMES,
          FIRST_REFRESH_TIME,
          LAST_REFRESH_TIME,
          FIRST_CHANGE_TIME,/*10*/
          LAST_CHANGE_TIME,
          24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM FIRST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) FIRST_REFRESH_TS,
          24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM LAST_REFRESH_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) LAST_REFRESH_TS,
          24 * 3600 * extract(day FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + 3600 * extract(hour FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + 60 * extract(minute FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) + extract(second FROM LAST_REFRESH_TIME - FIRST_REFRESH_TIME) REFRESH_TS,
          24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM FIRST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) FIRST_CHANGE_TS, /*15*/
          24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) + extract(second FROM LAST_CHANGE_TIME - TO_DATE('1970-01-01', 'YYYY-MM-DD')) LAST_CHANGE_TS,
          24 * 3600 * extract(day FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + 3600 * extract(hour FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + 60 * extract(minute FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) + extract(second FROM LAST_CHANGE_TIME - FIRST_CHANGE_TIME) CHANGE_TS,
          OTHERSTAT_1_ID,
          OTHERSTAT_1_VALUE,
          OTHERSTAT_2_ID,/*20*/
          OTHERSTAT_2_VALUE,
          OTHERSTAT_3_ID,
          OTHERSTAT_3_VALUE,
          OTHERSTAT_4_ID,
          OTHERSTAT_4_VALUE,/*25*/
          OTHERSTAT_5_ID,
          OTHERSTAT_5_VALUE,
          OTHERSTAT_6_ID,
          OTHERSTAT_6_VALUE
        FROM
          sys.gv$sql_plan_monitor
        WHERE
            trace_id = '##REPLACE_TRACE_ID##'
        ORDER BY
          ##REPLACE_ORDER_BY##
        '''
)

sql_dict.set_value(
    "sql_plan_monitor_detail_template_mysql",
    '''
        select
          PLAN_DEPTH,/*0*/
          SVR_IP,
          SVR_PORT,
          PROCESS_NAME,
          PLAN_LINE_ID,
          PLAN_OPERATION, /*5*/
          OUTPUT_ROWS,
          STARTS RESCAN_TIMES,
          FIRST_REFRESH_TIME,
          LAST_REFRESH_TIME,
          FIRST_CHANGE_TIME,/*10*/
          LAST_CHANGE_TIME,
          UNIX_TIMESTAMP(FIRST_REFRESH_TIME) FIRST_REFRESH_TS,
          UNIX_TIMESTAMP(LAST_REFRESH_TIME) LAST_REFRESH_TS,
          UNIX_TIMESTAMP(LAST_REFRESH_TIME) - UNIX_TIMESTAMP(FIRST_REFRESH_TIME) REFRESH_TS,
          UNIX_TIMESTAMP(FIRST_CHANGE_TIME) FIRST_CHANGE_TS, /*15*/
          UNIX_TIMESTAMP(LAST_CHANGE_TIME) LAST_CHANGE_TS,
          UNIX_TIMESTAMP(LAST_CHANGE_TIME) - UNIX_TIMESTAMP(FIRST_CHANGE_TIME) CHANGE_TS,
          OTHERSTAT_1_ID,
          OTHERSTAT_1_VALUE,
          OTHERSTAT_2_ID, /*20*/
          OTHERSTAT_2_VALUE,
          OTHERSTAT_3_ID,
          OTHERSTAT_3_VALUE,
          OTHERSTAT_4_ID,
          OTHERSTAT_4_VALUE, /*25*/
          OTHERSTAT_5_ID,
          OTHERSTAT_5_VALUE,
          OTHERSTAT_6_ID,
          OTHERSTAT_6_VALUE
        from
          oceanbase.gv$sql_plan_monitor
        where
            trace_id = '##REPLACE_TRACE_ID##'
        ORDER BY
          ##REPLACE_ORDER_BY##
        '''
)

sql_dict.set_value(
    "sql_plan_monitor_dfo_op_oracle_obversion4",
    '''
SELECT
  AVG("ROWS") EST_ROWS, /*0 */
  plan_monitor.PLAN_DEPTH,
  plan_monitor.PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME, /* 5 */
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  MIN(FIRST_REFRESH_TS) MIN_FIRST_REFRESH_TS,
  MAX(LAST_REFRESH_TS) MAX_LAST_REFRESH_TS, /*10 */
  MIN(FIRST_CHANGE_TS) MIN_FIRST_CHANGE_TS,
  MAX(LAST_CHANGE_TS) MAX_LAST_CHANGE_TS,
  AVG(REFRESH_TS) AVG_REFRESH_TIME,
  MAX(REFRESH_TS) MAX_REFRESH_TIME,
  MIN(REFRESH_TS) MIN_REFRESH_TIME,/*15 */
  AVG(CHANGE_TS) AVG_CHANGE_TIME,
  MAX(CHANGE_TS) MAX_CHANGE_TIME,
  MIN(CHANGE_TS) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS) - MIN(LAST_CHANGE_TS-FIRST_CHANGE_TS)) / MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS+0.000000001) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,/* 20 */
  MAX(OTHERSTAT_1_ID) OTHERSTAT_1_ID,
  SUM(OTHERSTAT_1_VALUE) SUM_STAT_1,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1, /* 25 */
  MAX(OTHERSTAT_2_ID) OTHERSTAT_2_ID,
  SUM(OTHERSTAT_2_VALUE) SUM_STAT_2,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2, /* 30 */
  MAX(OTHERSTAT_3_ID) OTHERSTAT_3_ID,
  SUM(OTHERSTAT_3_VALUE) SUM_STAT_3,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3, /* 35 */
  MAX(OTHERSTAT_4_ID) OTHERSTAT_4_ID,
  SUM(OTHERSTAT_4_VALUE) SUM_STAT_4,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4, /* 40 */
  MAX(OTHERSTAT_5_ID) OTHERSTAT_5_ID,
  SUM(OTHERSTAT_5_VALUE) SUM_STAT_5,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5, /* 45*/
  MAX(OTHERSTAT_6_ID) OTHERSTAT_6_ID,
  SUM(OTHERSTAT_6_VALUE) SUM_STAT_6,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6 /* 50 */,
  TRUNC(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNC(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNC(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
FROM
(select
  PLAN_DEPTH,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  OUTPUT_ROWS,
  STARTS,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,
  LAST_CHANGE_TIME,
  OTHERSTAT_1_ID,
  OTHERSTAT_2_ID,
  OTHERSTAT_3_ID,
  OTHERSTAT_4_ID,
  OTHERSTAT_5_ID,
  OTHERSTAT_6_ID,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_VALUE,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_VALUE,
  24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_REFRESH_TS,
  24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_REFRESH_TS,
  24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_CHANGE_TS,
  24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_CHANGE_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) REFRESH_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) CHANGE_TS,
DB_TIME,
USER_IO_WAIT_TIME
from
  sys.gv$sql_plan_monitor
  where
    trace_id = '##REPLACE_TRACE_ID##'

) plan_monitor
LEFT JOIN
(
 SELECT "ROWS", PLAN_LINE_ID FROM sys.##REPLACE_PLAN_EXPLAIN_TABLE_NAME## WHERE plan_id = ##REPLACE_PLAN_ID## AND tenant_id = ##REPLACE_TENANT_ID##
) plan_explain
ON
  plan_monitor.PLAN_LINE_ID = plan_explain.PLAN_LINE_ID
GROUP BY
  plan_monitor.PLAN_LINE_ID, PLAN_OPERATION, plan_monitor.PLAN_DEPTH
ORDER BY
  plan_monitor.PLAN_LINE_ID ASC;
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_dfo_op_mysql_obversion4",
    '''
select
  AVG(ROWS) EST_ROWS, /*0*/
  plan_monitor.PLAN_DEPTH PLAN_DEPTH,
  plan_monitor.PLAN_LINE_ID PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,/*5*/
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  UNIX_TIMESTAMP(MIN(FIRST_REFRESH_TIME)) MIN_FIRST_REFRESH_TS,
  UNIX_TIMESTAMP(MAX(LAST_REFRESH_TIME)) MAX_LAST_REFRESH_TS, /*10*/
  UNIX_TIMESTAMP(MIN(FIRST_CHANGE_TIME)) MIN_FIRST_CHANGE_TS,
  UNIX_TIMESTAMP(MAX(LAST_CHANGE_TIME)) MAX_LAST_CHANGE_TS,
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) AVG_REFRESH_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MAX_REFRESH_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MIN_REFRESH_TIME, /*15 */
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) AVG_CHANGE_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MAX_CHANGE_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) - MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME))) / MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)+0.00000001) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,/* 20 */
  MAX(OTHERSTAT_1_ID) OTHERSTAT_1_ID,
  SUM(OTHERSTAT_1_VALUE) SUM_STAT_1,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1, /* 25 */
  MAX(OTHERSTAT_2_ID) OTHERSTAT_2_ID,
  SUM(OTHERSTAT_2_VALUE) SUM_STAT_2,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2, /* 30 */
  MAX(OTHERSTAT_3_ID) OTHERSTAT_3_ID,
  SUM(OTHERSTAT_3_VALUE) SUM_STAT_3,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3, /* 35 */
  MAX(OTHERSTAT_4_ID) OTHERSTAT_4_ID,
  SUM(OTHERSTAT_4_VALUE) SUM_STAT_4,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4, /* 40 */
  MAX(OTHERSTAT_5_ID) OTHERSTAT_5_ID,
  SUM(OTHERSTAT_5_VALUE) SUM_STAT_5,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5, /* 45*/
  MAX(OTHERSTAT_6_ID) OTHERSTAT_6_ID,
  SUM(OTHERSTAT_6_VALUE) SUM_STAT_6,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6 /* 50 */,
  TRUNCATE(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNCATE(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNCATE(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
from
(
  select * FROM oceanbase.gv$sql_plan_monitor
where
  trace_id = '##REPLACE_TRACE_ID##'
) plan_monitor
LEFT JOIN
(
 SELECT ROWS, PLAN_LINE_ID FROM oceanbase.##REPLACE_PLAN_EXPLAIN_TABLE_NAME## WHERE plan_id = ##REPLACE_PLAN_ID## AND tenant_id = ##REPLACE_TENANT_ID##
) plan_explain
ON
  plan_monitor.PLAN_LINE_ID = plan_explain.PLAN_LINE_ID
GROUP BY
  plan_monitor.PLAN_LINE_ID, plan_monitor.PLAN_OPERATION
ORDER BY
  plan_monitor.PLAN_LINE_ID ASC;
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_svr_agg_template_oracle_obversion4",
    '''
SELECT
  PLAN_DEPTH,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  MIN(FIRST_REFRESH_TS) MIN_FIRST_REFRESH_TS,
  MAX(LAST_REFRESH_TS) MAX_LAST_REFRESH_TS,
  MIN(FIRST_CHANGE_TS) MIN_FIRST_CHANGE_TS,
  MAX(LAST_CHANGE_TS) MAX_LAST_CHANGE_TS,
  AVG(REFRESH_TS) AVG_REFRESH_TIME,
  MAX(REFRESH_TS) MAX_REFRESH_TIME,
  MIN(REFRESH_TS) MIN_REFRESH_TIME,
  AVG(CHANGE_TS) AVG_CHANGE_TIME,
  MAX(CHANGE_TS) MAX_CHANGE_TIME,
  MIN(CHANGE_TS) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS) - MIN(LAST_CHANGE_TS-FIRST_CHANGE_TS)) / MAX(LAST_CHANGE_TS-FIRST_CHANGE_TS+0.00000001) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,
  SVR_IP,
  SVR_PORT,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6,
  TRUNC(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNC(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNC(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
FROM
(
select
  PLAN_DEPTH,
  SVR_IP,
  SVR_PORT,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  OUTPUT_ROWS,
  STARTS,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,
  LAST_CHANGE_TIME,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_VALUE,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_VALUE,
  24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_REFRESH_TS,
  24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_REFRESH_TS,
  24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_CHANGE_TS,
  24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_CHANGE_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) REFRESH_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) CHANGE_TS,
  DB_TIME,
  USER_IO_WAIT_TIME
from
  sys.gv$sql_plan_monitor
  where
    trace_id = '##REPLACE_TRACE_ID##'
) tmp
GROUP BY
  PLAN_LINE_ID,PLAN_OPERATION,PLAN_DEPTH,SVR_IP,SVR_PORT
ORDER BY
  ##REPLACE_ORDER_BY##;
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_svr_agg_template_mysql_obversion4",
    '''
select
  PLAN_DEPTH,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  UNIX_TIMESTAMP(MIN(FIRST_REFRESH_TIME)) MIN_FIRST_REFRESH_TS,
  UNIX_TIMESTAMP(MAX(LAST_REFRESH_TIME)) MAX_LAST_REFRESH_TS,
  UNIX_TIMESTAMP(MIN(FIRST_CHANGE_TIME)) MIN_FIRST_CHANGE_TS,
  UNIX_TIMESTAMP(MAX(LAST_CHANGE_TIME)) MAX_LAST_CHANGE_TS,
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) AVG_REFRESH_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MAX_REFRESH_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MIN_REFRESH_TIME,
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) AVG_CHANGE_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MAX_CHANGE_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) - MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME))) / MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,
  SVR_IP,
  SVR_PORT,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6,
  TRUNCATE(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNCATE(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNCATE(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
from
  oceanbase.gv$sql_plan_monitor
where
  trace_id = '##REPLACE_TRACE_ID##'
GROUP BY
  PLAN_LINE_ID,PLAN_OPERATION,SVR_IP,SVR_PORT
ORDER BY
  ##REPLACE_ORDER_BY##; 
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_db_time_mysql_template_obversion4",
    '''
SELECT  COUNT(1) THREAD_NUM, PLAN_LINE_ID, PLAN_DEPTH, PLAN_OPERATION, SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
   TRUNCATE(AVG(DB_TIME)/1000000000.0/2.5, 2) MY_DB_TIME,
   TRUNCATE(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 2) MY_CPU_TIME,
   TRUNCATE(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 2) MY_IO_TIME
FROM oceanbase.gv$sql_plan_monitor
WHERE trace_id = '##REPLACE_TRACE_ID##'
GROUP BY PLAN_LINE_ID, PLAN_OPERATION, PLAN_DEPTH
ORDER BY PLAN_LINE_ID
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_db_time_oracle_template_obversion4",
    '''
SELECT  COUNT(1) THREAD_NUM, PLAN_LINE_ID, PLAN_DEPTH, PLAN_OPERATION, SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
   TRUNC(AVG(DB_TIME)/1000000000.0/2.5, 2) MY_DB_TIME,
   TRUNC(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 2) MY_CPU_TIME,
   TRUNC(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 2) MY_IO_TIME
FROM SYS.gv$sql_plan_monitor
WHERE trace_id = '##REPLACE_TRACE_ID##'
GROUP BY PLAN_LINE_ID, PLAN_OPERATION, PLAN_DEPTH
ORDER BY PLAN_LINE_ID
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_detail_template_oracle_obversion4",
    '''
select
  PLAN_DEPTH, /*0*/
  SVR_IP,
  SVR_PORT,
  PROCESS_NAME,
  PLAN_LINE_ID,
  PLAN_OPERATION,/*5*/
  OUTPUT_ROWS,
  STARTS RESCAN_TIMES,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,/*10*/
  LAST_CHANGE_TIME,
  24 * 3600 * extract(day FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_REFRESH_TS,
  24 * 3600 * extract(day FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_REFRESH_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_REFRESH_TS,
  24 * 3600 * extract(day FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM FIRST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) FIRST_CHANGE_TS,
  24 * 3600 * extract(day FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM LAST_CHANGE_TIME - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS.FF')) LAST_CHANGE_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_REFRESH_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) REFRESH_TS,
  24 * 3600 * extract(day FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 3600 * extract(hour FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + 60 * extract(minute FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) + extract(second FROM TO_TIMESTAMP(LAST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF') - TO_TIMESTAMP(FIRST_CHANGE_TIME, 'YYYY-MM-DD HH24:MI:SS.FF')) CHANGE_TS,
  OTHERSTAT_1_ID,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_ID,/*20*/
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_ID,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_ID,
  OTHERSTAT_4_VALUE,/*25*/
  OTHERSTAT_5_ID,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_ID,
  OTHERSTAT_6_VALUE,
  TRUNC((DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNC((DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNC((USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
FROM
  sys.gv$sql_plan_monitor
WHERE
    trace_id = '##REPLACE_TRACE_ID##'
ORDER BY
  ##REPLACE_ORDER_BY##;
    '''
)

sql_dict.set_value(
    "sql_plan_monitor_detail_template_mysql_obversion4",
    '''
select
  PLAN_DEPTH,/*0*/
  SVR_IP,
  SVR_PORT,
  PROCESS_NAME,
  PLAN_LINE_ID,
  PLAN_OPERATION, /*5*/
  OUTPUT_ROWS,
  STARTS RESCAN_TIMES,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,/*10*/
  LAST_CHANGE_TIME,
  UNIX_TIMESTAMP(FIRST_REFRESH_TIME) FIRST_REFRESH_TS,
  UNIX_TIMESTAMP(LAST_REFRESH_TIME) LAST_REFRESH_TS,
  UNIX_TIMESTAMP(LAST_REFRESH_TIME) - UNIX_TIMESTAMP(FIRST_REFRESH_TIME) REFRESH_TS,
  UNIX_TIMESTAMP(FIRST_CHANGE_TIME) FIRST_CHANGE_TS, /*15*/
  UNIX_TIMESTAMP(LAST_CHANGE_TIME) LAST_CHANGE_TS,
  UNIX_TIMESTAMP(LAST_CHANGE_TIME) - UNIX_TIMESTAMP(FIRST_CHANGE_TIME) CHANGE_TS,
  OTHERSTAT_1_ID,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_ID, /*20*/
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_ID,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_ID,
  OTHERSTAT_4_VALUE, /*25*/
  OTHERSTAT_5_ID,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_ID,
  OTHERSTAT_6_VALUE,
  TRUNCATE((DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNCATE((DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNCATE((USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
from
  oceanbase.gv$sql_plan_monitor
where
    trace_id = '##REPLACE_TRACE_ID##'
ORDER BY
  ##REPLACE_ORDER_BY##;
    '''
)
